package dbms;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBSelectingFunctions {
	
	public static ResultSet getUsers() throws SQLException
	{
		Statement st = DBConnection.getIstance().createStatement();
		return st.executeQuery("SELECT * FROM " + DBTables.UTENTE);
	}
	
	public static ResultSet getProgramParameter(String programName) throws SQLException
	{
		String sql = "SELECT * FROM " + DBTables.PROGRAM + " WHERE name = ?";
		PreparedStatement ps = DBConnection.getIstance().prepareStatement(sql);
		ps.setString(1, programName);
		
		return ps.executeQuery();
	}

	public static ResultSet getPrograms() throws SQLException
	{
		Statement st = DBConnection.getIstance().createStatement();
		return st.executeQuery("SELECT name, extenzion FROM " + DBTables.PROGRAM);
	}
	
	public static ResultSet getAssociatedPrograms(String userId) throws SQLException
	{
		String sql = "SELECT program FROM " + DBTables.PROG_UTENTE + " WHERE utente=?";
		PreparedStatement ps = DBConnection.getIstance().prepareStatement(sql);
		ps.setString(1, userId);
		
		return ps.executeQuery();
	}
	
	public static ResultSet getNotAssociatedPrograms(String userId) throws SQLException
	{
		String sql = "SELECT name FROM " + DBTables.PROGRAM + " " +
					 "EXCEPT (SELECT program FROM " + DBTables.PROG_UTENTE + " WHERE utente=?)";
		PreparedStatement ps = DBConnection.getIstance().prepareStatement(sql);
		ps.setString(1, userId);
		
		return ps.executeQuery();
	}
	
	public static ResultSet getAssociatedUsers(String programName) throws SQLException
	{
		String sql = "SELECT utente FROM " + DBTables.PROG_UTENTE + " WHERE program=?";
		PreparedStatement ps = DBConnection.getIstance().prepareStatement(sql);
		ps.setString(1, programName);
		
		return ps.executeQuery();
	}
	
	public static ResultSet getFiles(String programName) throws SQLException
	{
		String sql = "SELECT * FROM " + DBTables.FILE + " WHERE program=?";
		PreparedStatement ps = DBConnection.getIstance().prepareStatement(sql);
		ps.setString(1, programName);
		
		return ps.executeQuery();
	}
	
	public static ResultSet getReleases(int fileId) throws SQLException
	{
		String sql = "SELECT * FROM " + DBTables.RELEASE + " WHERE file=?";
		PreparedStatement ps = DBConnection.getIstance().prepareStatement(sql);
		ps.setInt(1, fileId);
		
		return ps.executeQuery();
	}
	
	public static Integer getFileId(String programName, String fileName) throws SQLException
	{
		String sql = "SELECT id FROM " + DBTables.FILE + " WHERE program=? AND name=?";
		PreparedStatement ps = DBConnection.getIstance().prepareStatement(sql);
		ps.setString(1, programName);
		ps.setString(2, fileName);
	
		return DBGeneralFunctions.returnIntFromSingleValueResultSet(ps.executeQuery());		
	}
	
	public static int getLastReleaseOfAFile(int fileId) throws SQLException
	{
		String sql = "SELECT release_number FROM " + DBTables.FILE + " WHERE id=?";
		PreparedStatement ps = DBConnection.getIstance().prepareStatement(sql);
		ps.setInt(1, fileId);
		
		return DBGeneralFunctions.returnIntFromSingleValueResultSet(ps.executeQuery());
	}	
}